﻿CREATE TABLE [dbo].[DocumenteDetaliu] (
    [IDDocumentDetaliu]    BIGINT          IDENTITY (1, 1) NOT NULL,
    [IDDocument]           BIGINT          NOT NULL,
    [IDArticol]            INT             NOT NULL,
    [Cantitate]            DECIMAL (25, 5) CONSTRAINT [DF_DocumenteDetaliu_Cantitate] DEFAULT ((0)) NOT NULL,
    [ValoareUnitara]       DECIMAL (25, 5) CONSTRAINT [DF_DocumenteDetaliu_PretUnitar] DEFAULT ((0)) NOT NULL,
    [ValoareTVA]           DECIMAL (25, 5) CONSTRAINT [DF_DocumenteDetaliu_ValoareTVA] DEFAULT ((0)) NOT NULL,
    [ValoareTotala]        DECIMAL (25, 5) NOT NULL,
    [CodUnitate]           SMALLINT        CONSTRAINT [DF_DocumenteDetaliu_CodUnitate] DEFAULT ((0)) NOT NULL,
    [IDUnitateMasura]      SMALLINT        CONSTRAINT [DF_DocumenteDetaliu_IDUnitateMasura] DEFAULT ((0)) NOT NULL,
    [IDRegimFiscal]        SMALLINT        CONSTRAINT [DF_DocumenteDetaliu_IDRegimFiscal] DEFAULT ((0)) NOT NULL,
    [CotaTVA]              DECIMAL (5, 2)  CONSTRAINT [DF_DocumenteDetaliu_CotaTVA] DEFAULT ((0)) NOT NULL,
    [TermenLivrare]        DATETIME        NULL,
    [IDVariantaArticol]    INT             NULL,
    [Observatii]           VARCHAR (250)   NULL,
    [ValoareTotalaCuAdaos] DECIMAL (25, 5) CONSTRAINT [DF_DocumenteDetaliu_ValoareTotalaCuAdaos] DEFAULT ((0)) NOT NULL,
    [ArtAl]                VARCHAR (40)    NULL,
    [TVAIncasare]          BIT             DEFAULT ((0)) NOT NULL,
    [ValoareUnitaraValuta] DECIMAL (25, 5) DEFAULT ((0)) NOT NULL,
    [DataCreation]         DATETIME        DEFAULT (getdate()) NOT NULL,
    [CantComandata]        AS              ([dbo].[GetCantitateComandataSiReceptionata]([IDDocumentDetaliu],[IDArticol])),
    [StocIntrat]           AS              ([dbo].[GetCantitateStocIntrata]([IDDocumentDetaliu],[IDArticol])),
    CONSTRAINT [PK_DocumenteDetaliu] PRIMARY KEY CLUSTERED ([IDDocumentDetaliu] ASC),
    CONSTRAINT [FK_DocumenteDetaliu_Articole] FOREIGN KEY ([IDArticol]) REFERENCES [dbo].[Articole] ([IDArticol]),
    CONSTRAINT [FK_DocumenteDetaliu_ArticoleAliniate] FOREIGN KEY ([ArtAl], [CodUnitate]) REFERENCES [dbo].[ArticoleAliniate] ([ArtAl], [CodUnitate]),
    CONSTRAINT [FK_DocumenteDetaliu_ArticoleVariante] FOREIGN KEY ([IDVariantaArticol]) REFERENCES [dbo].[ArticoleVariante] ([IDVariantaArticol]),
    CONSTRAINT [FK_DocumenteDetaliu_Documente] FOREIGN KEY ([IDDocument]) REFERENCES [dbo].[Documente] ([IDDocument]),
    CONSTRAINT [FK_DocumenteDetaliu_RegimFiscal] FOREIGN KEY ([IDRegimFiscal]) REFERENCES [dbo].[RegimFiscal] ([IDRegimFiscal]),
    CONSTRAINT [FK_DocumenteDetaliu_Unitati] FOREIGN KEY ([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate]),
    CONSTRAINT [FK_DocumenteDetaliu_UnitatiMasura] FOREIGN KEY ([IDUnitateMasura]) REFERENCES [dbo].[UnitatiMasura] ([IDUnitateMasura])
);


GO


CREATE TRIGGER [dbo].[Stoc_DocumenteDetaliu_Insert]
   ON  [dbo].[DocumenteDetaliu]
   AFTER INSERT,UPDATE,DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @GestiuneCantitativa bit
	SET @GestiuneCantitativa = 0
	SELECT @GestiuneCantitativa = GestiuneCantitativa FROM FaresSetup fs JOIN inserted i ON i.CodUnitate = fs.CodUnitate
	
	
	--SE ADAUGA POZITIA IN STOCURI FARA VALORI
	IF @GestiuneCantitativa = 0
	BEGIN
		INSERT INTO Stocuri 
		([IDArticol],[CodUnitate],[Pret],[StocInitial],[StocPrecedent],[IDDepartament],[IDVarinataArticol],PretCodValuta,PretValuta)
		SELECT i.IDArticol, d.CodUnitate, i.ValoareUnitara,0,0,null,i.IDVariantaArticol,d.CodValuta,i.ValoareUnitaraValuta
		FROM inserted i JOIN  Documente d ON i.IDDocument = d.IDDocument JOIN TipDocument td ON td.IDTipDocument = d.IDTipDocument
		WHERE
		td.ModificaStoc=1 AND td.TipOperatie like '%I%' AND
		Not exists(SELECT * FROM Stocuri  s where s.IDArticol=i.IDArticol AND s.CodUnitate = d.CodUnitate AND s.Pret=i.ValoareUnitara and 
		((s.IDVarinataArticol = i.IDVariantaArticol) or ((s.IDVarinataArticol is null) and (i.IDVariantaArticol is null))))
	END
	ELSE
	BEGIN
		INSERT INTO Stocuri 
		([IDArticol],[CodUnitate],[Pret],[StocInitial],[StocPrecedent],[IDDepartament],[IDVarinataArticol],PretCodValuta,PretValuta)
		SELECT i.IDArticol, d.CodUnitate,0,0,0,null,i.IDVariantaArticol,d.CodValuta,i.ValoareUnitaraValuta
		FROM inserted i JOIN  Documente d ON i.IDDocument = d.IDDocument JOIN TipDocument td ON td.IDTipDocument = d.IDTipDocument
		WHERE
		td.ModificaStoc=1 AND td.TipOperatie like '%I%' AND
		Not exists(SELECT * FROM Stocuri  s where s.IDArticol=i.IDArticol AND s.CodUnitate = d.CodUnitate AND s.Pret=0 and 
		((s.IDVarinataArticol = i.IDVariantaArticol) or ((s.IDVarinataArticol is null) and (i.IDVariantaArticol is null))))
	END
	
END